import xlrd
import pandas as pd
import os
import shutil
import re
from glob import glob
from tqdm import tqdm
from pony import orm
from typing import Union
from decimal import Decimal as DC, ROUND_HALF_UP

from postgres.db_build import MedicineNormalize, ServiceNormalize, ConsumableNormalize, Hospital_coding, \
    Hospital_mapping, Country, Hospital, ChineseMedicineNormalize, ServiceVersion, HospitalBiecheng
from utils.post_process import post_process, medicine_normalize, service_normalize
from utils.DataProcess import DataProcess
from utils.yibao2medicine import yibao2medicine
from utils.module.medicine_normalization import MedicineNormalizaiton
from utils.module.medical_service_normalization import MedicalServiceNormalization
from utils.module.medical_consumables_normalization import ConsumablesNormalization
from utils.common_utils import url_2_link


class HospitalBussinessDataProcess(object):
    def __init__(self, dir_path):
        self.dir_path = dir_path
        self.date = dir_path.split("/")[-1]
        # 建立目录结构

        self.addr = ""
        self.data_process = DataProcess()
        self.consumables_normalization = ConsumablesNormalization()
        # 业务库表头
        self.columns = ["hospital_id", "hospital", "img_original_name", "full_name", "customer_name", "nhsa_name",
                        "nhsa_code", "price", "medical_insurance_type", "ratio", "type", "remark", "img_path",
                        "quantity_amount"]
        # 添加后处理的pass_flag
        self.columns.insert(self.columns.index("customer_name") + 1, "pass_flag")
        # 后处理自动通过标志
        self.post_pass_flag = 2
        # 添加归一化的pass_flag
        self.columns.insert(self.columns.index("nhsa_name") + 1, "pass_flag2")
        # 归一化自动通过标志
        self.normalize_pass_flag = 1
        # 后处理人工审核表头
        self.post_process_manual_columns = ["full_name", "customer_name", "pass_flag"]
        # 大项人工审核表头
        self.type_manual_columns = ["full_name", "customer_name", "type"]

        # 输入文件路径
        self.input_file_path = os.path.join(self.dir_path, "2、解析结果.xlsx")
        # 预处理，合并去重结果
        self.pre_process_file_path = os.path.join(self.dir_path, "2.1、合并去重.xlsx")
        # 后处理结果
        self.post_process_file_path = os.path.join(self.dir_path, "3、后处理.xlsx")
        # 人工处理文件
        self.post_process_manual_file_path = os.path.join(self.dir_path, "3.1、后处理人工审核.xlsx")
        # 人工审核后回填文件
        self.post_process_update_file_path = os.path.join(self.dir_path, "4、更新后处理数据.xlsx")
        # 添加大项文件
        self.add_type_file_path = os.path.join(self.dir_path, "5、添加大项.xlsx")
        self.add_type_manual_file_path = os.path.join(self.dir_path, "5.1、人工添加大项.xlsx")
        self.add_type_update_file_path = os.path.join(self.dir_path, "6、更新大项.xlsx")
        self.output_file_path = os.path.join(self.dir_path, f"{self.date}可入库inphile_big_data_hospital.xlsx")
        # 冗余项
        self.add_parts_file_path = os.path.join(self.dir_path, "7、冗余项.xlsx")

        self.menu = str("请输入需要运行的程序：\n"
                        "1、合并去重\n"
                        "2、后处理\n"
                        "3、后处理人工审核结果回填\n"
                        "4、添加大项\n"
                        "5、冗余项拆分\n"
                        "6、生成入库文件\n"
                        "输入其他跳过当前流程\n"
                        "回车键确认\n")

        self.large_name_list = ["材料费", "卫生材料", "卫生材料费", "中草药费", "草药费", "中草药", "中药饮片", "中药饮片费", "中药饮品",
                                "中药饮品费", "名贵中草药", "中成药费", "中成药", "西药费", "西药", "疫苗费", "检查费", "放射费", "放射线",
                                "一般治疗操作费", "治疗费", "手术费", "麻醉费", "理疗费", "护理费", "洗牙费", "牙修复费", "配镜费",
                                "化验费", "检验费", "实验室诊断费", "其他", "其他费", "其他费用", "其他门急诊费", "其他门急诊费用",
                                "其他门诊收费", "其他收入", "其他住院费", "其他住院费用", "其它", "其它费", "其它收入", "中成费",
                                "中药费"]

        # 通过医院名称搜索医院编码

    # 四舍五入
    def rounding(self, num: Union[str, DC, float, int], prec: int = 2) -> str:
        if not num and num != 0:
            return ''
        elif isinstance(num, str) and (',' in num or '，' in num):
            return num
        elif isinstance(num, str):
            num = DC(num)
        elif isinstance(num, (float, int)):
            num = DC(str(num))
        else:
            pass
        return str(num.quantize(DC(f"0.{''.join(['0' for _ in range(prec)])}"), rounding=ROUND_HALF_UP))

    # 统一为英文符号
    def ch_en(self, word):
        # 统一为英文符号
        intab = u"！—。【】“”‘’《》—「」『』〖〗〔〕〓︰﹒﹕﹙﹚﹝﹞﹢〈〉∽∶‥–‒Ι×[]（）＃［］{}：％＊﹛﹜，－／÷＿﹡；ⅹ\\"
        outab = u"!-.()\"\"\'\'()-()()()()=:.:()()+()~::--i*()()#()():%*(),-//_*;*/"
        trantab = str.maketrans(intab, outab)  # 一一映射。
        word = word.translate(trantab)  # 返回字符串S的副本，其中每个字符都已通过给定的转换表进行映射。
        # 统一罗马数字
        word = word.replace("Ⅰ", "I")
        word = word.replace("Ⅱ", "II")
        word = word.replace("Ⅲ", "III")
        word = word.replace("Ⅳ", "IV")
        word = word.replace("Ⅴ", "V")
        word = word.replace("Ⅵ", "VI")
        word = word.replace("Ⅶ", "VII")
        word = word.replace("Ⅷ", "VIII")
        word = word.replace("Ⅸ", "IX")
        word = word.replace("Ⅹ", "X")

        if re.findall("＜.*?＞", word):
            word = word.replace("＜", "(")
            word = word.replace("＞", ")")
        if re.findall("<.*?>", word):
            word = word.replace("<", "(")
            word = word.replace(">", ")")
        # 空格处理
        word = re.sub(
            "(?<=[^0-9a-zA-Z])[ ]+(?=[0-9a-zA-Z])|(?<=[0-9a-zA-Z])[ ]+(?=[^0-9a-zA-Z])|"
            "(?<=[^0-9a-zA-Z])[ ]+(?=[^0-9a-zA-Z])|(?<=[()])[ ]+|[ ]+(?=[()\-])",
            "", word)
        return word

    # 输入医院名称，匹配标准名称和编码
    @orm.db_session
    def get_hospital_coding(self, hospital):
        hospital_id = ''
        name = hospital
        qs = orm.select(
            code
            for biecheng in HospitalBiecheng for code in Hospital_coding
            if
            biecheng.another_name == hospital
            and
            biecheng.name == code.hospital_name_nhsa
        )
        if qs.count():
            for item in qs:
                hospital_id = item.hospital_id
                name = item.hospital_name_nhsa
                break
        else:
            qs = orm.select(
                item
                for item in Hospital_mapping
                if
                item.hospital_name_bill == hospital
            )
            if qs.count():
                for item in qs:
                    hospital_id = item.hospital_id
                    name = item.hospital_name_nhsa
                    break
        return (hospital_id, name)

    # 输入文件校验
    def input_check(self, columns, path):
        """
        输入校验：
        输入文件表头必须是医院网站库表头的子集，表头命名必须一致
        """
        if set(columns) <= set(self.columns):
            pass
        else:
            raise Exception(f"{os.path.basename(path)}文件表头有误")

    # 后处理审核完成后更新输入文件校验
    def manual_input_check(self, columns, path):
        """
        输入校验：
        输入文件表头必须包含后处理人工审核的所有表头，表头命名必须一致
        """
        if set(columns) >= set(self.post_process_manual_columns):
            pass
        else:
            raise Exception(f"{os.path.basename(path)}文件表头有误")

    # 临时计算单价用于词条归类
    def caculate_price(self, quantity_amount):
        if "_" in quantity_amount:
            num, amount = tuple(quantity_amount.split(";")[0].split("_"))
            price = self.rounding(DC(amount) / DC(num))
            return price
        else:
            return ""

    # 去除大项
    def is_large_name(self, name):
        if name in self.large_name_list:
            return True
        else:
            return False

    # 合并去重,更新医院
    def drop_duplicate(self):
        path = self.input_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df_tmp = df.copy()
        # 去除完全重复项
        df = df.drop_duplicates(
            ["hospital", "full_name", "price", "medical_insurance_type", "ratio", "quantity_amount"])
        # 去除大项
        df = df[~df["full_name"].apply(lambda x: self.is_large_name(x))]
        # 临时计算单价
        df["price"] = df["quantity_amount"].apply(lambda x: self.caculate_price(x))
        # 按医院名称、原值、单价、数量_金额对排序
        df = df.sort_values(["hospital", "full_name", "price", "quantity_amount"])
        df = df.reset_index(drop=True)
        # 按医院名称、原值、医保类型、自负比例、单价筛选重复值
        df_is_duplicate = df.duplicated(["hospital", "full_name", "medical_insurance_type", "ratio", "price"])
        tmp = 0
        for index in tqdm(range(0, len(df))):
            # 跳过重复段
            if tmp > 0:
                tmp -= 1
                continue
            # 如果是重复值，找到最后一个重复值的index
            if df_is_duplicate.iloc[index]:
                for index_tail in range(index, len(df)):
                    if not df_is_duplicate.iloc[index_tail]:
                        index = index - 1
                        tmp = index_tail - index - 2
                        # img_path
                        img_paths = list()
                        # 数量金额对
                        quantity_amount = set()
                        for i in range(index, index_tail):
                            num_before = len(quantity_amount)
                            quantity_amount.add(df.loc[i, "quantity_amount"])
                            num_after = len(quantity_amount)
                            if num_after > num_before:
                                img_paths.append(df.loc[i, "img_path"])
                        quantity_amount = list(quantity_amount)
                        img_paths = list(img_paths)
                        df.loc[index, "quantity_amount"] = ";".join(quantity_amount)
                        df.loc[index, "img_path"] = ";".join(img_paths)
                        break
        # 去重保留第一个
        df = df.drop_duplicates(["hospital", "full_name", "medical_insurance_type", "ratio", "price"], keep="first")
        # 去除数量不为1的项目的单价
        df_bool = df["quantity_amount"].apply(lambda x: bool(re.search("(?<![0-9])1_", x)))
        df.loc[:, "price"][~df_bool] = ""
        df = df.fillna("")
        df.to_excel(self.pre_process_file_path, index=False)

    # 后处理自动通过
    @orm.db_session()
    def post_auto_pass(self, customer_name, full_name):
        # TODO:存在漏洞，无法确定词条在所有表中模糊匹配可以得到的条数，如果一个词条在一个库中精准匹配，在另一个库存在包含关系，则会误打标签为2
        # TODO:根本上解决需要构建新的数据库，将所有词条包含进去
        # 先匹配原值统一中英文符号后的结果，能匹配上，直接用原值作为甲方回传值
        # 过甲方回传库和归一化标准词条，精准匹配：
        # 能匹配上，再进行包含匹配：
        # 能匹配上且只匹配上一个，后处理值自动通过，标签为2；
        # 匹配到多个，但是原值和甲方值相同，自动通过，标签为2；
        # 匹配到多个，但是原值和甲方值不同，需要人看一下，标签为1；

        # full_name = self.ch_en(full_name)
        if "特需" in full_name:
            return 2, customer_name
        pass_flag = 0
        qs = Country.select(customer_name=customer_name)
        if qs.count() > 0:
            qs = orm.count(
                item.customer_name
                for item in Country
                if customer_name in item.customer_name)
            if qs == 1:
                pass_flag = 2
            elif qs > 1:
                if customer_name == full_name:
                    pass_flag = 2
                else:
                    pass_flag = 1
        else:
            qs1 = orm.count(
                medicine
                for medicine in ChineseMedicineNormalize
                if full_name in medicine.standard_name
                or full_name in medicine.other_name
            )
            if qs1 > 0:
                pass_flag = 2
                customer_name = full_name
            else:
                qs2 = ChineseMedicineNormalize.select(standard_name=customer_name)
                if qs2.count() > 0:
                    qs2 = orm.count(
                        item.standard_name
                        for item in ChineseMedicineNormalize
                        if customer_name in item.standard_name)
                    if qs2 == 1:
                        pass_flag = 2
                    elif qs2 > 1:
                        if customer_name == full_name:
                            pass_flag = 2
                        else:
                            pass_flag = 1
                else:
                    qs3 = ChineseMedicineNormalize.select(other_name=customer_name)
                    if qs3.count() > 0:
                        qs3 = orm.count(
                            item.other_name
                            for item in ChineseMedicineNormalize
                            if customer_name in item.other_name)
                        if qs3 == 1:
                            pass_flag = 2
                        elif qs3 > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1

            if pass_flag == 0:
                qs2 = orm.count(
                    service
                    for service in ServiceVersion
                    if full_name in service.version_name
                )
                if qs2 > 0:
                    pass_flag = 2
                    customer_name = full_name
                else:
                    qs2 = ServiceVersion.select(version_name=customer_name)
                    if qs2.count() > 0:
                        qs2 = orm.count(
                            item.standard_name
                            for item in ServiceNormalize
                            if customer_name in item.standard_name)
                        if qs2 == 1:
                            pass_flag = 2
                        elif qs2 > 1:
                            if customer_name == full_name:
                                pass_flag = 2
                            else:
                                pass_flag = 1
                        else:
                            pass_flag = 2
            if pass_flag == 0:
                qs1 = orm.count(
                    consumable
                    for consumable in ConsumableNormalize
                    if full_name in consumable.fourth_level_name
                    or full_name in consumable.third_level_name
                    or full_name in consumable.common_name
                    or full_name in consumable.fourth_keyword_synonym
                )
                if qs1 > 0:
                    pass_flag = 2
                    customer_name = full_name
                else:
                    qs1 = orm.select(
                        consumable
                        for consumable in ConsumableNormalize
                        if consumable.fourth_level_name == customer_name
                        or consumable.third_level_name == customer_name
                        or consumable.common_name == customer_name
                        or consumable.fourth_keyword_synonym == customer_name
                    )
                    if qs1.count() > 0:
                        pass_flag = 2

        return pass_flag, customer_name

    # 后处理
    def post_process(self):
        print("开始后处理")
        path = self.pre_process_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        # 输入校验
        self.input_check(df.columns.tolist(), path)

        table = []
        for index in tqdm(range(0, len(df), 200)):
            cache = []
            input_par = []
            for k in range(0, 200):
                if k + index >= len(df):
                    continue
                # 医院网站库格式
                item = df.iloc[index + k, :]
                series = pd.Series(item, index=self.columns)
                full_name = item["full_name"]
                if full_name:
                    # 后处理
                    input_par.append(
                        {
                            "医院名称": "",
                            "费用名称": full_name,
                            "number": "",
                            "amount": "",
                            "mapping_name": ""
                        })
                    cache.append(series)
                else:
                    table.append(series)

            res = post_process(input_par)
            for k in range(0, len(res)):
                pass_flag = 0
                customer_name = res[k]["mapping_name"]
                full_name = res[k]["name"]
                flag = res[k]["remark"]
                series = cache[k]
                if flag:
                    pass_flag = 2
                else:
                    pass_flag, customer_name = self.post_auto_pass(customer_name, full_name)
                if customer_name:
                    series["customer_name"] = customer_name
                    series["pass_flag"] = pass_flag
                table.append(series)
            if index % 1000 == 0 or (index + 200) > len(df):
                df_output = pd.DataFrame(table)
                df_output = df_output.fillna("")
                df_output.to_excel(self.post_process_file_path, index=False)
                df_check = df_output[df_output["pass_flag"] != self.post_pass_flag][self.post_process_manual_columns]
                df_check.drop_duplicates(inplace=True)
                df_check.to_excel(self.post_process_manual_file_path, index=False)

    # 后处理人工审核后回填
    def update_manual_data_post(self):
        print("开始后处理人工审核值回填")
        manual_path = self.post_process_manual_file_path
        path = self.post_process_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        # 输入校验
        self.manual_input_check(df1.columns.tolist(), manual_path)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            df.loc[:, "customer_name"][item["full_name"] == df["full_name"]] = item["customer_name"]
            df.loc[:, "pass_flag"][item["full_name"] == df["full_name"]] = self.post_pass_flag
        df = df[~(df["pass_flag"] == "0")]
        df.to_excel(self.post_process_update_file_path, index=False)

    # 添加大项
    @orm.db_session
    def add_type(self):
        print("开始添加大项")
        path = self.post_process_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df_output = df.copy()
        for index, item in tqdm(df_output.iterrows(), total=len(df_output)):
            writed = 1
            if not item["type"]:
                customer_name = item["customer_name"]
                writed = 0
                # 西药中成药
                qs1 = orm.select(
                    medicine.nhsa_code
                    for medicine in MedicineNormalize
                    if (medicine.trade_name and medicine.trade_name in customer_name)
                    or (medicine.principal_component and medicine.principal_component in customer_name)
                )
                if qs1.count() > 0:
                    for it in qs1:
                        if len(it) > 0:
                            if it[0] == "X":
                                _type = "西药费"
                                item["type"] = _type
                                writed = 1
                                break
                            elif it[0] == "Z":
                                _type = "中成药费"
                                item["type"] = _type
                                writed = 1
                                break
                # 中药饮片
                if not writed:
                    qs2 = orm.count(
                        CM
                        for CM in ChineseMedicineNormalize
                        if CM.standard_name == customer_name
                        or CM.other_name == customer_name
                        or (CM.standard_name and CM.standard_name in customer_name)
                        or (CM.other_name and CM.other_name in customer_name)
                    )
                    if qs2 > 0:
                        _type = "中药饮片"
                        item["type"] = _type
                        writed = 1

                # 甲方回传大库
                if not writed:
                    qs = orm.select(
                        details.type
                        for details in Country
                        if details.type
                        and details.customer_name == customer_name
                    )
                    if qs.count() > 0:
                        for it in qs:
                            if len(it) > 2:
                                _type = it
                                item["type"] = _type
                                break
                    else:
                        qs = orm.select(
                            details.type
                            for details in Country
                            if details.type
                            and details.customer_name in customer_name
                        )
                        if qs.count() > 0:
                            for it in qs:
                                if len(it) > 2:
                                    _type = it
                                    item["type"] = _type
                                    break
            if not writed or not item["quantity_amount"]:
                img_path = item["img_path"]
                url = img_path.split(";")[0]
                dir_name = os.path.dirname(url)
                file_name = os.path.basename(url).split(".")[0]
                files = os.listdir(dir_name)
                link_path = dir_name
                for file in files:
                    if file_name in file:
                        if "pdf" in file:
                            link_path = os.path.join(dir_name, file)
                            break

                link = url_2_link(img_path, link_path)
                item["img_path"] = link

        df_output.to_excel(self.add_type_file_path, index=False)
        df_check = df_output[df_output["type"] == ""][self.type_manual_columns]
        df_check.drop_duplicates(inplace=True)
        df_check.to_excel(self.add_type_manual_file_path, index=False)

    # 生成入库文件
    def output(self):
        print("生成入库文件中...")
        manual_path = self.add_type_manual_file_path
        path = self.add_type_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        df1 = pd.read_excel(manual_path, dtype=str, keep_default_na=False)
        for index, item in tqdm(df1.iterrows(), total=len(df1)):
            if item["type"]:
                df.loc[:, "type"][item["full_name"] == df["full_name"]] = item["type"]
            if "=HYPERLINK" in item["img_path"]:
                df.loc[:, "item"][item[""] == df["full_name"]] = item["type"]
        for index, item in tqdm(df.iterrows(), total=len(df)):
            if "=HYPERLINK" in item["img_path"]:
                item["img_path"] = item["img_path"].split('"')[-2]
        df.to_excel(self.add_type_update_file_path, index=False)
        if ((df["pass_flag"] != "2") & (df["pass_flag"] != 2)).any():
            print("后处理中存在未通过的条目，请检查pass_flag")
        if (df["type"] == "").any():
            print("大项存在空值，需要人工填充")
        shutil.copy(self.add_type_update_file_path, self.output_file_path)

    # 计算冗余项
    def add_parts(self):
        print("开始计算冗余项")
        path = self.post_process_update_file_path
        df = pd.read_excel(path, dtype=str, keep_default_na=False)
        table_parts = []
        for index, series in tqdm(df.iterrows(), total=len(df)):
            if series["full_name"]:
                series_parts = pd.Series(series, index=["full_name", "customer_name",
                                                        "sub_name1", "sub_name2", "sub_name3",
                                                        "specification",
                                                        "manufacturer",
                                                        "special_symbol", "others"])

                standard_name = series_parts["customer_name"]
                other_name = series_parts["full_name"]
                other_name = self.data_process.ch_en(other_name)
                if standard_name:
                    cust_name, parts = self.data_process.split_parts(other_name)
                    other_parts = self.data_process.deleted_words(other_name, standard_name)
                    if parts:
                        for k, v in parts.items():
                            if v in standard_name:
                                parts[k] = ""
                            if k != "其他":
                                for i in re.split("[ ()]", v):
                                    if i and i in other_parts:
                                        other_parts = other_parts.replace(i, "")
                                        other_parts = other_parts.strip()
                        if not re.search("[^()（）]", other_parts):
                            other_parts = ""

                        if not re.search("[0-9a-zA-Z\u4e00-\u9fa5]", other_parts):
                            other_parts = ""
                        if other_parts != parts["其他"]:
                            parts["其他"] += self.data_process.deleted_words(other_parts, parts["其他"])
                        text = parts["其他"]
                        text = text.replace("()", "")
                        text = text.replace("( )", "")
                        text = text.strip()
                        parts["其他"] = text
                        for i, part in enumerate(parts["医用术语"].split(" ")):
                            if i < 3:
                                series_parts[f"sub_name{i + 1}"] = part
                        if not series_parts["specification"]:
                            series_parts["specification"] += parts["规格"]
                        if not series_parts["manufacturer"]:
                            series_parts["manufacturer"] = parts["药厂"]
                        series_parts["special_symbol"] = parts["特殊符号"]
                        series_parts["others"] = parts["其他"]
                table_parts.append(series_parts)
        df_output = pd.DataFrame(table_parts, dtype=str)
        df_output = df_output.fillna("")
        df_output.to_excel(self.add_parts_file_path, index=False)

    # 主入口
    def main(self):
        print("业务数据处理：")
        if not os.path.exists(self.input_file_path):
            print("输入文件不存在或命名错误")
            return 0
        key = input(self.menu)
        if "1" in key:
            self.drop_duplicate()
        if "2" in key:
            self.post_process()
        if "3" in key:
            self.update_manual_data_post()
        if "4" in key:
            self.add_type()
        if "5" in key:
            self.add_parts()
        if "6" in key:
            self.output()


if __name__ == '__main__':
    dirs = []
    dir_path = "/home/kv/workspace/太养明细名称数据清洗/0601-0608"
    hospital_business_process = HospitalBussinessDataProcess(dir_path)
    hospital_business_process.main()
